package com._520it.day1._06_smis.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com._520it.day1._06_smis.dao.IStudentDAO;
import com._520it.day1._06_smis.domain.Student;

//步骤：贾琏欲执事
public class StudentDAOImpl_bak1_old implements IStudentDAO {

	@Override
	public void add(Student stu) {
		StringBuilder sb=new StringBuilder("INSERT INTO t_student(name,age) VALUES(");
		sb.append("'").append(stu.getName()).append("',");
		sb.append(stu.getAge());
		sb.append(")");
		// 声明需要关闭的资源
		Connection conn = null;
		Statement st = null;
		try {
			// 可能出线异常的代码
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");
			st = conn.createStatement();
			st.executeUpdate(sb.toString());
			} catch (Exception e) {
					e.printStackTrace();
			} finally {
					// 释放资源
				try {
				 if (st != null) {
					 st.close();
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					try {
						if (conn != null) {
							conn.close();
						}
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
	}

	@Override
	public void delete(Long id) {
		String sql="DELETE FROM t_student WHERE id="+id;
		// 声明需要关闭的资源
		Connection conn = null;
		Statement st = null;
		try {
			// 可能出线异常的代码
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");
			st = conn.createStatement();
			st.executeUpdate(sql);
			} catch (Exception e) {
					e.printStackTrace();
			} finally {
					// 释放资源
				try {
				 if (st != null) {
					 st.close();
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					try {
						if (conn != null) {
							conn.close();
						}
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
	}

	@Override
	public void update(Student newStu) {
		StringBuilder sb=new StringBuilder("UPDATE t_student SET ");
		sb.append("name= '").append(newStu.getName()).append("',");
		sb.append("age=").append(newStu.getAge());
		sb.append(" WHERE id= ").append(newStu.getId());
		System.out.println(sb);
		// 声明需要关闭的资源
		Connection conn = null;
		Statement st = null;
		try {
			// 可能出线异常的代码
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");
			st = conn.createStatement();
			st.executeUpdate(sb.toString());
			} catch (Exception e) {
					e.printStackTrace();
			} finally {
					// 释放资源
				try {
				 if (st != null) {
					 st.close();
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					try {
						if (conn != null) {
							conn.close();
						}
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
	}

	@Override
	public Student get(Long id) {
		String sql="SELECT * FROM t_student WHERE id="+id;
		// 声明需要关闭的资源
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// 可能出线异常的代码
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			//处理结果集
			if (rs.next()) {
				Student stu = new Student();
				stu.setId(rs.getLong("id"));
				stu.setName(rs.getString("name"));
				stu.setAge(rs.getInt("age"));
				return stu;
				}
			} catch (Exception e) {
					e.printStackTrace();
			} finally {
					// 释放资源
			try {
			if (rs != null) {
				rs.close();
			}
					} catch (Exception e) {
						e.printStackTrace();
					} finally {
						try {
							if (st != null) {
								st.close();
							}
						} catch (Exception e) {
							e.printStackTrace();
						} finally {
							try {
								if (conn != null) {
									conn.close();
								}
							} catch (Exception e) {
								e.printStackTrace();
							}
						}
					}
				}		
		return null;
	}

	@Override
	public List<Student> list() {
		String sql = "SELECT * FROM t_student";
		List<Student> list = new ArrayList<>();
		// 声明需要关闭的资源
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// 可能出线异常的代码
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			//处理结果集
			while (rs.next()) {
				Student stu = new Student();
				list.add(stu);
				stu.setId(rs.getLong("id"));
				stu.setName(rs.getString("name"));
				stu.setAge(rs.getInt("age"));
	
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 释放资源
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					if (st != null) {
						st.close();
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					try {
						if (conn != null) {
							conn.close();
						}
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
		}
		return list;
	}

}
